package servicios.biziGasteizAdmin;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.StringReader;
import java.net.URI;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ListIterator;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.io.IOUtils;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.xml.sax.InputSource;

import connection.BDConnection;

/**
 * Servlet implementation class RestaurantServices
 */
public class RestaurantServices extends HttpServlet {
	private static final long serialVersionUID = 1L;
    private String CONTENT_TYPE = "application/json";
    /**
     * @see HttpServlet#HttpServlet()
     */
    public RestaurantServices() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		boolean isMultipart = ServletFileUpload.isMultipartContent(request);
		if(!isMultipart)
			processRequest(request, response);
		else
			try {
				processMultipartRequest(request, response);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		boolean isMultipart = ServletFileUpload.isMultipartContent(request);
		if(!isMultipart)
			processRequest(request, response);
		else
			try {
				processMultipartRequest(request, response);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	
	private void processRequest(HttpServletRequest request, HttpServletResponse response) throws IOException{
		response.setContentType(CONTENT_TYPE);
		String requestP = request.getParameter("request");
		try {
			if(requestP.compareToIgnoreCase("GETRESTAURANTS") == 0){
					getRestaurants(request, response);
			}
			else if(requestP.compareToIgnoreCase("GETMENUSOFERTS") == 0){
				getMenuOferts(request, response);
			}
			else if(requestP.compareToIgnoreCase("DELETEROW") == 0){
				deleteRow(request, response);
			}
			else if(requestP.compareToIgnoreCase("DELETEMENU") == 0){
				deleteMenu(request, response);
			}
			else{
				response.getWriter().print("{\"success\":false, \"data\": \"Request desconocido\"}");
			}
		} catch (Exception e) {
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
		}
	}
	private void processMultipartRequest(HttpServletRequest request, HttpServletResponse response) throws IOException, JSONException{
		response.setContentType(CONTENT_TYPE);
		String requestP = request.getParameter("request");
		try{
			if(requestP.compareToIgnoreCase("NEWMENU") == 0){
				newMenu(request, response);
			}
			else if(requestP.compareToIgnoreCase("UPDATEMENU") == 0){
				updateMenu(request, response);
			}
			else if(requestP.compareToIgnoreCase("NEWRESTAURANT") == 0){
				newRestaurant(request, response);
			}   
			else if(requestP.compareToIgnoreCase("UPDATERESTAURANT") == 0){
				updateRestaurant(request, response);
			}
			else{
				response.getWriter().print("{\"success\":false, \"data\": \"Request desconocido\"}");
			}
		}
		catch(Exception e){
			JSONObject resJson = new JSONObject();
			resJson.put("success", false);
			e.printStackTrace();
			response.getWriter().print(resJson);
		}
	}
	
	private String getGeocodedPoint(String street){
		String URL = "http://maps.googleapis.com/maps/api/geocode/xml";
		try{
			URL url = new URL(URL + "?address=" + URLEncoder.encode(street, "UTF-8")+ "&sensor=false");	
			// Open the Connection
			URLConnection conn = url.openConnection();
			//This is Simple a byte array output stream that we will use to keep the output data from google.
			ByteArrayOutputStream output = new ByteArrayOutputStream(1024);	
			// copying the output data from Google which will be either in JSON or XML depending on your request URL that in which format you have requested.
			IOUtils.copy(conn.getInputStream(), output);
			//close the byte array output stream now.
			output.close();
	
			String outPut = output.toString();
			DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		    DocumentBuilder builder = factory.newDocumentBuilder();
		    InputSource is = new InputSource(new StringReader(outPut));
		      
		    Document d = builder.parse(is);
		    
		    String status = d.getElementsByTagName("status").item(0).getFirstChild().getNodeValue();
		    if(status.equals("OK")){
		    	Element location  = (Element)d.getElementsByTagName("location").item(0);
		    	
		    	String lng = location.getElementsByTagName("lng").item(0).getFirstChild().getNodeValue();
		    	String lat = location.getElementsByTagName("lat").item(0).getFirstChild().getNodeValue();
		    	
		    	return "ST_GeomFromText('POINT("+lng+" "+lat+")', 4326)";
		    }
		    else{
		    	return null;
		    }
		}catch(Exception o){
			return null;
		}
	}
	
	private void updateRestaurant(HttpServletRequest request, HttpServletResponse  response) throws SQLException, IOException, ClassNotFoundException{
		
	}
	
	private void newRestaurant(HttpServletRequest request, HttpServletResponse  response) throws SQLException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		String values = "";
		String columns= "";
		String sqlQuery = "";
		try{
			List items = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
			ListIterator iterator = items.listIterator();
	        while (iterator.hasNext()) {
	        	FileItem item = (FileItem) iterator.next();
	            if (item.isFormField()) {
	                String fieldname = item.getFieldName();
	                String fieldvalue = item.getString();
	                if(fieldname.equals("tipo_bar")){
	                	values = values + "'"+fieldvalue +"',";
	                	columns = columns + "tipo" +",";
	                }
	                else if(fieldname.equals("calle")){
	                	values = values + "'"+fieldvalue +"',";
	                	columns = columns + fieldname +",";
	                	String geocoded = this.getGeocodedPoint(fieldvalue);
	                	if(geocoded != null){
	                		values = values + geocoded +",";
		                	columns = columns + "localizacion" +",";
	                	}
	                }
	                else{
	                	values = values + "'"+fieldvalue +"',";
	                	columns = columns + fieldname +",";
	                }
	            } else {
	                String fieldname = item.getFieldName();
	                String filename = item.getName();
	                if(!filename.equals("")){
		                fieldname = fieldname.replace("_upload_restaurant", "");
		                InputStream filecontent = item.getInputStream();
		                values = values + "'./images/eventos/restaurantes/"+filename+"',";
		                columns = columns + fieldname+",";
		                
		                String relativePath = request.getSession().getServletContext().getRealPath("/images/eventos/restaurantes");
		                File f=new File(relativePath+File.separator+filename);//Aqui le dan el nombre y/o con la ruta del archivo salida
		                OutputStream os=new FileOutputStream(f);
		                byte[] buf =new byte[1024];//Actualizado me olvide del 1024
		                int len;
		                while((len=filecontent.read(buf))>0){
		                	os.write(buf,0,len);
		                }
		                os.close();
		                filecontent.close();
	                }
	            }
	        }
	        values = values.substring(0,values.length() -1);
	        columns = columns.substring(0, columns.length() -1);
	        
        	sqlQuery = "INSERT INTO restaurantes ("+columns+") VALUES ("+values+")";
        	System.out.println(sqlQuery);
        	JSONObject resJson = new JSONObject();
	        int rs = bdCon.executeUpdate(sqlQuery);
	        if(rs > 0){
				resJson.put("success", true);
			}
			else{
				resJson.put("success", false);
			}
			response.getWriter().write(resJson.toString());
			bdCon.closeConnection();
	        
		}catch(Exception o){
			o.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+o.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	private void deleteRow(HttpServletRequest request, HttpServletResponse  response) throws SQLException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		String restauranteId = request.getParameter("restauranteid");
		String deleteRestaurantSql = "DELETE FROM restaurantes WHERE restaurantid="+restauranteId;
		String deleteMenusSql = "DELETE FROM menu WHERE restauranteid="+restauranteId;
		JSONObject res = new JSONObject();
		try{
			int rows = bdCon.executeUpdate(deleteRestaurantSql);
			if(rows > 0){
				rows = bdCon.executeUpdate(deleteMenusSql);
				res.put("success", true);
			}
			else{
				res.put("success", false);
			}
			bdCon.closeConnection();
			
			response.getWriter().print(res);
		}
		catch(Exception e){
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	private void deleteMenu(HttpServletRequest request, HttpServletResponse  response) throws SQLException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		String menuid = request.getParameter("menuid");
		String deleteRestaurantSql = "DELETE FROM menu WHERE menuid='"+menuid+"'";
		JSONObject res = new JSONObject();
		try{
			int rows = bdCon.executeUpdate(deleteRestaurantSql);
			if(rows > 0){
				res.put("success", true);
			}
			else{
				res.put("success", false);
			}
			bdCon.closeConnection();
			
			response.getWriter().print(res);
		}
		catch(Exception e){
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	private void getMenuOferts(HttpServletRequest request, HttpServletResponse  response) throws SQLException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			String restauranteId = request.getParameter("restauranteid");
			String sqlStr = "SELECT * FROM menu where restauranteid = "+restauranteId+" ORDER BY tipo";
			
			
			ResultSet rs = bdCon.executeQuery(sqlStr);
			JSONArray dataArray = new JSONArray();
			JSONArray desayunoArray = new JSONArray();
			JSONArray comidaArray = new JSONArray();
			JSONArray cenaArray = new JSONArray();
			JSONArray pintxoArray = new JSONArray();
			JSONArray ofertaArray = new JSONArray();
			boolean cena = false;
			boolean comida = false;
			boolean desayuno = false;
			boolean pintxo = false;
			boolean oferta = false;
			
			while(rs.next()){
				JSONObject menuIem = new JSONObject();
				String tipo = rs.getString("tipo");
				if(tipo.equals("Cena")){
					cena = true;
					menuIem.put("leaf", true);
					menuIem.put("text", rs.getString("nombre"));
					menuIem.put("precio", rs.getDouble("precio"));
					menuIem.put("descripcion", rs.getString("descripcion"));
					menuIem.put("menuid", rs.getString("menuid"));
					menuIem.put("hora_desde", rs.getTime("hora_desde"));
					menuIem.put("hora_hasta", rs.getTime("hora_hasta"));
					menuIem.put("foto", rs.getString("foto"));
					menuIem.put("restauranteid", rs.getInt("restauranteid"));
					menuIem.put("tipo", rs.getString("tipo"));
					cenaArray.put(menuIem);
				}
				else if(tipo.equals("Comida")){
					comida = true;
					menuIem.put("leaf", true);
					menuIem.put("text", rs.getString("nombre"));
					menuIem.put("precio", rs.getDouble("precio"));
					menuIem.put("descripcion", rs.getString("descripcion"));
					menuIem.put("menuid", rs.getString("menuid"));
					menuIem.put("hora_desde", rs.getTime("hora_desde"));
					menuIem.put("hora_hasta", rs.getTime("hora_hasta"));
					menuIem.put("foto", rs.getString("foto"));
					menuIem.put("restauranteid", rs.getInt("restauranteid"));
					menuIem.put("tipo", rs.getString("tipo"));
					comidaArray.put(menuIem);
				}
				else if(tipo.equals("Desayuno")){
					desayuno = true;
					menuIem.put("leaf", true);
					menuIem.put("text", rs.getString("nombre"));
					menuIem.put("precio", rs.getDouble("precio"));
					menuIem.put("descripcion", rs.getString("descripcion"));
					menuIem.put("menuid", rs.getString("menuid"));
					menuIem.put("hora_desde", rs.getTime("hora_desde"));
					menuIem.put("hora_hasta", rs.getTime("hora_hasta"));
					menuIem.put("foto", rs.getString("foto"));
					menuIem.put("restauranteid", rs.getInt("restauranteid"));
					menuIem.put("tipo", rs.getString("tipo"));
					desayunoArray.put(menuIem);
				}
				else if(tipo.equals("Pintxo")){
					pintxo = true;
					menuIem.put("leaf", true);
					menuIem.put("text", rs.getString("nombre"));
					menuIem.put("precio", rs.getDouble("precio"));
					menuIem.put("descripcion", rs.getString("descripcion"));
					menuIem.put("menuid", rs.getString("menuid"));
					menuIem.put("hora_desde", rs.getTime("hora_desde"));
					menuIem.put("hora_hasta", rs.getTime("hora_hasta"));
					menuIem.put("foto", rs.getString("foto"));
					menuIem.put("restauranteid", rs.getInt("restauranteid"));
					menuIem.put("tipo", rs.getString("tipo"));
					pintxoArray.put(menuIem);
				}
				else if(tipo.equals("Oferta")){
					oferta = true;
					menuIem.put("leaf", true);
					menuIem.put("text", rs.getString("nombre"));
					menuIem.put("precio", rs.getDouble("precio"));
					menuIem.put("descripcion", rs.getString("descripcion"));
					menuIem.put("menuid", rs.getString("menuid"));
					menuIem.put("hora_desde", rs.getTime("hora_desde"));
					menuIem.put("hora_hasta", rs.getTime("hora_hasta"));
					menuIem.put("foto", rs.getString("foto"));
					menuIem.put("restauranteid", rs.getInt("restauranteid"));
					menuIem.put("tipo", rs.getString("tipo"));
					ofertaArray.put(menuIem);
				}
			}
			JSONObject cenaNodo = new JSONObject();
			cenaNodo.put("text", "Cenas");
			if(cena){
				cenaNodo.put("children", cenaArray);
				cenaNodo.put("expanded", true);
			}
			else{
				cenaNodo.put("leaf", true);
			}
			JSONObject comidaNodo = new JSONObject();
			comidaNodo.put("text", "Comidas");
			if(comida){
				comidaNodo.put("children", comidaArray);
				comidaNodo.put("expanded", true);
			}
			else{
				comidaNodo.put("leaf", true);
			}
			JSONObject desayunoNodo = new JSONObject();
			desayunoNodo.put("text", "Desayunos");
			if(desayuno){
				desayunoNodo.put("children", desayunoArray);
				desayunoNodo.put("expanded", true);
			}
			else{
				desayunoNodo.put("leaf", true);
			}
			JSONObject pintxoNodo = new JSONObject();
			pintxoNodo.put("text", "Pintxos");
			if(pintxo){
				pintxoNodo.put("children", pintxoArray);
				pintxoNodo.put("expanded", true);
			}
			else{
				pintxoNodo.put("leaf", true);
			}
			JSONObject ofertaNodo = new JSONObject();
			ofertaNodo.put("text", "Ofertas");
			if(oferta){
				ofertaNodo.put("children", ofertaArray);
				ofertaNodo.put("expanded", true);
			}
			else{
				ofertaNodo.put("leaf", true);
			}
			dataArray.put(desayunoNodo);
			dataArray.put(comidaNodo);
			dataArray.put(cenaNodo);
			dataArray.put(ofertaNodo);
			dataArray.put(pintxoNodo);
			
			response.getWriter().write(dataArray.toString());
			
			bdCon.closeConnection(rs);
		}
		catch(Exception e){
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	private void getRestaurants(HttpServletRequest request, HttpServletResponse  response) throws SQLException, IOException, ClassNotFoundException{
		BDConnection bdCon = new BDConnection();
		try{
			
			String sqlStr = "SELECT * FROM RESTAURANTES";
			ResultSet rs = bdCon.executeQuery(sqlStr);
			JSONObject resJson = new JSONObject();
			resJson.put("success", true);
			JSONArray itemsArray = new JSONArray(); 
			while(rs.next()){
				JSONObject item = new JSONObject();
				item.put("nombre", rs.getString("nombre"));
				item.put("descripcion_corta", rs.getString("descripcion_corta"));
				item.put("horario", rs.getString("horario"));
				item.put("restaurantid", rs.getString("restaurantid"));
				item.put("tipo", rs.getString("tipo"));
				item.put("foto_principal", rs.getString("foto_principal"));
				item.put("descripcion", rs.getString("descripcion"));
				item.put("localizacion", rs.getString("localizacion"));
				item.put("calle", rs.getString("calle"));
				itemsArray.put(item);
			}
			resJson.put("data", itemsArray);
			
			response.getWriter().write(resJson.toString());
			
			bdCon.closeConnection(rs);
		}
		catch(Exception e){
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	private void newMenu(HttpServletRequest request, HttpServletResponse  response) throws ClassNotFoundException, IOException{
		BDConnection bdCon = new BDConnection();
		String values = "";
		String columns= "";
		String sqlQuery = "";
		try{
			List items = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
			ListIterator iterator = items.listIterator();
	        while (iterator.hasNext()) {
	        	FileItem item = (FileItem) iterator.next();
	        	 if (item.isFormField()) {
	        		 String fieldname = item.getFieldName();
		             String fieldvalue = item.getString();
		             if(fieldname.equals("precio")){
		            	 values = values + fieldvalue +",";
		            	 columns = columns + fieldname +",";
		             }
		             else if(fieldname.equals("restauranteid")){
		            	 values = values + fieldvalue +",";
		            	 columns = columns + fieldname +",";
		             }
		             else if(fieldname.equals("menuid")){
		             }
		             else{
		            	 values = values + "'"+fieldvalue +"',";
		            	 columns = columns + fieldname +",";
	                }
	        	 }
	        	 else {
	                String fieldname = item.getFieldName();
	                String filename = item.getName();
	                if(!filename.equals("")){
		                InputStream filecontent = item.getInputStream();
		                values = values + "'./images/eventos/restaurantes/"+filename+"',";
		                columns = columns + fieldname+",";
		                String relativePath = request.getSession().getServletContext().getRealPath("/images/eventos/restaurantes");
		                File f=new File(relativePath+File.separator+filename);//Aqui le dan el nombre y/o con la ruta del archivo salida
		                OutputStream os=new FileOutputStream(f);
		                byte[] buf =new byte[1024];//Actualizado me olvide del 1024
		                int len;
		                while((len=filecontent.read(buf))>0){
		                	os.write(buf,0,len);
		                }
		                os.close();
		                filecontent.close();
	                }
	            }
	        }
	        values = values.substring(0,values.length() -1);
        	columns = columns.substring(0, columns.length() -1);
        	sqlQuery = "INSERT INTO menu ("+columns+") VALUES ("+values+")";
        	System.out.println(sqlQuery);
        	JSONObject resJson = new JSONObject();
	        int rs = bdCon.executeUpdate(sqlQuery);
	        if(rs > 0){
				resJson.put("success", true);
			}
			else{
				resJson.put("success", false);
			}
			response.getWriter().write(resJson.toString());
			bdCon.closeConnection();
		}
		catch(Exception e){
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
	
	private void updateMenu(HttpServletRequest request, HttpServletResponse  response) throws ClassNotFoundException, IOException{
		BDConnection bdCon = new BDConnection();
		String updates = "";
		String sqlQuery = "";
		String restauranteid = "";
		String menuid = "";
		try{
			List items = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
			ListIterator iterator = items.listIterator();
	        while (iterator.hasNext()) {
	        	FileItem item = (FileItem) iterator.next();
	        	 if (item.isFormField()) {
	        		 String fieldname = item.getFieldName();
		             String fieldvalue = item.getString();
		             if(fieldname.equals("precio")){
		            	 updates = updates+" "+fieldname+"="+fieldvalue+",";
		             }
		             else if(fieldname.equals("restauranteid")){
		            	 restauranteid = fieldvalue;
		             }
		             else if(fieldname.equals("menuid")){
		            	 menuid = fieldvalue;
		             }
		             else{
		            	 updates = updates+" "+fieldname+"='"+fieldvalue+"',";
	                }
	        	 }
	        	 else {
	                String fieldname = item.getFieldName();
	                String filename = item.getName();
	                if(!filename.equals("")){
		                InputStream filecontent = item.getInputStream();
		                updates = updates+" "+fieldname+"='./images/eventos/restaurantes/"+filename+"',";
		                String relativePath = request.getSession().getServletContext().getRealPath("/images/eventos/restaurantes");
		                File f=new File(relativePath+File.separator+filename);//Aqui le dan el nombre y/o con la ruta del archivo salida
		                OutputStream os=new FileOutputStream(f);
		                byte[] buf =new byte[1024];//Actualizado me olvide del 1024
		                int len;
		                while((len=filecontent.read(buf))>0){
		                	os.write(buf,0,len);
		                }
		                os.close();
		                filecontent.close();
	                }
	            }
	        }
	        updates = updates.substring(0, updates.length()-1);
        	sqlQuery = "UPDATE  menu SET "+updates+" where restauranteid="+restauranteid+" and menuid="+menuid;
        	System.out.println(sqlQuery);
        	JSONObject resJson = new JSONObject();
	        int rs = bdCon.executeUpdate(sqlQuery);
	        if(rs > 0){
				resJson.put("success", true);
			}
			else{
				resJson.put("success", false);
			}
			response.getWriter().write(resJson.toString());
			bdCon.closeConnection();
		}
		catch(Exception e){
			e.printStackTrace();
			response.getWriter().print("{\"success\":false, \"data\": \""+e.getMessage()+"\"}");
			bdCon.closeConnection();
		}
	}
}
